﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZGEDrySaltery.DbHelper;
using ZGEDrySaltery.Model;

namespace ZGEDrySaltery.DAL
{
    public class OfferDAL
    {
        #region 单例模式
        private static OfferDAL instance;

        public static OfferDAL GetInstance()
        {
            if (instance == null)
            {
                lock (typeof(OfferDAL))
                {
                    if (instance == null)
                    {
                        instance = new OfferDAL();
                    }
                }
            }
            return instance;
        }
        private OfferDAL()
        {
        }

        #endregion

        #region 获取商品列表
        /// <summary>
        /// 分页获取获取商品列表
        /// </summary>
        /// <param name="starindex">开始下标</param>
        /// <param name="pagesize">分页大小</param>
        /// <param name="pcid">分类ID</param>
        /// <param name="status">状态</param>
        /// <param name="title">商品标题</param>
        /// <returns></returns>
        public List<OfferModel> GetOfferList(int starindex, int pagesize, int pcid, int status, string title)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.AppendFormat(@" SELECT a.*,b.`NAME` from offer a
                                   LEFT JOIN object_category b on a.PCID = b.PCID where 1=1");
            if (pcid != 0)
            {
                strsql.AppendFormat(" and a.PCID = {0}", pcid);
            }
            if (status != -1)
            {
                strsql.AppendFormat(" and STATE = {0}", status);
            }
            if (!string.IsNullOrEmpty(title))
            {
                strsql.AppendFormat(" and TITLE ={0}", title);
            }
            strsql.AppendFormat(" order by sort desc");

            if (starindex != 0)
            {
                strsql.AppendFormat(" limit {0},{1}", starindex, pagesize);
            }

            OfferModel unit = new OfferModel();
            List<OfferModel> list = new List<OfferModel>();
            try
            {
                using (IDataReader reader = DbHelperMySQL.ExecuteReader(strsql.ToString()))
                {
                    while (reader.Read())
                    {
                        unit = OffeToModel(reader);
                        list.Add(unit);
                    }
                }
            }
            catch
            {
                throw;
            }
            return list;
        }

        /// <summary>
        /// 转实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public OfferModel OffeToModel(IDataReader row)
        {
            OfferModel model = new OfferModel();
            if (row != null)
            {
                if (row["OFFERID"] != null && row["OFFERID"].ToString() != "")
                {
                    model.OFFERID = int.Parse(row["OFFERID"].ToString());
                }
                if (row["COMPANYID"] != null && row["COMPANYID"].ToString() != "")
                {
                    model.COMPANYID = int.Parse(row["COMPANYID"].ToString());
                }
                if (row["USERID"] != null && row["USERID"].ToString() != "")
                {
                    model.USERID = int.Parse(row["USERID"].ToString());
                }
                if (row["USERNAME"] != null)
                {
                    model.USERNAME = row["USERNAME"].ToString();
                }
                if (row["REALNAME"] != null)
                {
                    model.REALNAME = row["REALNAME"].ToString();
                }
                if (row["TITLE"] != null)
                {
                    model.TITLE = row["TITLE"].ToString();
                }
                if (row["UNIT"] != null)
                {
                    model.UNIT = row["UNIT"].ToString();
                }
                if (row["SPECIFICATIONSSTR"] != null)
                {
                    model.SPECIFICATIONSSTR = row["SPECIFICATIONSSTR"].ToString();
                }
                if (row["VIPPRICE"] != null && row["VIPPRICE"].ToString() != "")
                {
                    model.VIPPRICE = Decimal.Parse(row["VIPPRICE"].ToString());
                }
                if (row["MARKETPRICE"] != null && row["MARKETPRICE"].ToString() != "")
                {
                    model.MARKETPRICE = Decimal.Parse(row["MARKETPRICE"].ToString());
                }
                if (row["PCID"] != null && row["PCID"].ToString() != "")
                {
                    model.PCID = int.Parse(row["PCID"].ToString());
                }
                if (row["INTRO"] != null)
                {
                    model.INTRO = row["INTRO"].ToString();
                }
                if (row["AREA"] != null)
                {
                    model.AREA = row["AREA"].ToString();
                }
                if (row["SORT"] != null && row["SORT"].ToString() != "")
                {
                    model.SORT = int.Parse(row["SORT"].ToString());
                }
                if (row["IMAGES"] != null)
                {
                    model.IMAGES = row["IMAGES"].ToString();
                }
                if (row["SORT"] != null && row["SORT"].ToString() != "")
                {
                    model.SORT = int.Parse(row["SORT"].ToString());
                }
                if (row["INVENTORYCOUNT"] != null && row["INVENTORYCOUNT"].ToString() != "")
                {
                    model.INVENTORYCOUNT = int.Parse(row["INVENTORYCOUNT"].ToString());
                }
                if (row["STATE"] != null && row["STATE"].ToString() != "")
                {
                    model.STATE = int.Parse(row["STATE"].ToString());
                }
                if (row["MEMO"] != null && row["MEMO"].ToString() != "")
                {
                    model.MEMO = row["MEMO"].ToString();
                }
                if (row["NAME"] != null && row["NAME"].ToString() != "")
                {
                    model.PCNAME = row["NAME"].ToString();
                } 
            }

            return model;
        }
        #endregion 获取商品列表

        #region 获取商品总数
        /// <summary>
        /// 分页获取获取商品列表
        /// </summary>
        /// <param name="pcid">分类ID</param>
        /// <param name="status">状态</param>
        /// <param name="title">商品标题</param>
        /// <returns></returns>
        public int GetOfferCount(int pcid, int status, string title)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.AppendFormat(@" SELECT count(0) from Offer where 1=1");
            if (pcid != 0)
            {
                strsql.AppendFormat(" and PCID = {0}", pcid);
            }
            if (status != -1)
            {
                strsql.AppendFormat(" and STATE = {0}", status);
            }
            if (!string.IsNullOrEmpty(title))
            {
                strsql.AppendFormat(" and TITLE ={0}", title);
            }

            try
            {
                object obj = DbHelperMySQL.GetSingle(strsql.ToString());
                if (obj == null)
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt32(obj);
                }
            }
            catch
            {
                throw;
            }
        }
        #endregion

        #region 获取商品详情
        /// <summary>
        /// 获取商品详情
        /// </summary>
        /// <param name="offerid">商品ID</param>
        /// <returns></returns>
        public OfferModel GetFormOffer(int offerid)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.AppendFormat(@" SELECT a.*,b.`NAME` from offer a
                                   LEFT JOIN object_category b on a.PCID = b.PCID where offerid = {0}", offerid);
            OfferModel unit = new OfferModel();
            try
            {
                using (IDataReader reader = DbHelperMySQL.ExecuteReader(strsql.ToString()))
                {
                    while (reader.Read())
                    {
                        unit = OffeToModel(reader);
                    }
                }
            }
            catch
            {
                throw;
            }
            return unit;
        }

        #endregion 获取商品列表

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(OfferModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into offer(");
            strSql.Append("COMPANYID,USERID,USERNAME,REALNAME,TITLE,UNIT,SPECIFICATIONSSTR,VIPPRICE,MARKETPRICE,MINPRICE,PIFAPRICE,PCID,INTRO,AREA,RECOMMEND,SORT,IMAGES,INVENTORYCOUNT,TRADETOTAL,STATE,MEMO,INSERTTIME,MODIFYTIME,F1,F2,F3,F4)");
            strSql.Append(" values (");
            strSql.Append("@COMPANYID,@USERID,@USERNAME,@REALNAME,@TITLE,@UNIT,@SPECIFICATIONSSTR,@VIPPRICE,@MARKETPRICE,@MINPRICE,@PIFAPRICE,@PCID,@INTRO,@AREA,@RECOMMEND,@SORT,@IMAGES,@INVENTORYCOUNT,@TRADETOTAL,@STATE,@MEMO,@INSERTTIME,@MODIFYTIME,@F1,@F2,@F3,@F4)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@COMPANYID", MySqlDbType.Int32,11),
					new MySqlParameter("@USERID", MySqlDbType.Int32,11),
					new MySqlParameter("@USERNAME", MySqlDbType.VarChar,36),
					new MySqlParameter("@REALNAME", MySqlDbType.VarChar,36),
					new MySqlParameter("@TITLE", MySqlDbType.VarChar,512),
					new MySqlParameter("@UNIT", MySqlDbType.VarChar,32),
					new MySqlParameter("@SPECIFICATIONSSTR", MySqlDbType.VarChar,64),
					new MySqlParameter("@VIPPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@MARKETPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@MINPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@PIFAPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@PCID", MySqlDbType.Int32,11),
					new MySqlParameter("@INTRO", MySqlDbType.VarChar,1024),
					new MySqlParameter("@AREA", MySqlDbType.VarChar,64),
					new MySqlParameter("@RECOMMEND", MySqlDbType.Int32,11),
					new MySqlParameter("@SORT", MySqlDbType.Int32,11),
					new MySqlParameter("@IMAGES", MySqlDbType.VarChar,256),
					new MySqlParameter("@INVENTORYCOUNT", MySqlDbType.Int32,11),
					new MySqlParameter("@TRADETOTAL", MySqlDbType.Int32,11),
					new MySqlParameter("@STATE", MySqlDbType.Int32,11),
					new MySqlParameter("@MEMO", MySqlDbType.VarChar,256),
					new MySqlParameter("@INSERTTIME", MySqlDbType.DateTime),
					new MySqlParameter("@MODIFYTIME", MySqlDbType.DateTime),
					new MySqlParameter("@F1", MySqlDbType.VarChar,256),
					new MySqlParameter("@F2", MySqlDbType.VarChar,256),
					new MySqlParameter("@F3", MySqlDbType.VarChar,256),
					new MySqlParameter("@F4", MySqlDbType.VarChar,256)};
            parameters[0].Value = model.COMPANYID;
            parameters[1].Value = model.USERID;
            parameters[2].Value = model.USERNAME;
            parameters[3].Value = model.REALNAME;
            parameters[4].Value = model.TITLE;
            parameters[5].Value = model.UNIT;
            parameters[6].Value = model.SPECIFICATIONSSTR;
            parameters[7].Value = model.VIPPRICE;
            parameters[8].Value = model.MARKETPRICE;
            parameters[9].Value = model.MINPRICE;
            parameters[10].Value = model.PIFAPRICE;
            parameters[11].Value = model.PCID;
            parameters[12].Value = model.INTRO;
            parameters[13].Value = model.AREA;
            parameters[14].Value = model.RECOMMEND;
            parameters[15].Value = model.SORT;
            parameters[16].Value = model.IMAGES;
            parameters[17].Value = model.INVENTORYCOUNT;
            parameters[18].Value = model.TRADETOTAL;
            parameters[19].Value = model.STATE;
            parameters[20].Value = model.MEMO;
            parameters[21].Value = model.INSERTTIME;
            parameters[22].Value = model.MODIFYTIME;
            parameters[23].Value = model.F1;
            parameters[24].Value = model.F2;
            parameters[25].Value = model.F3;
            parameters[26].Value = model.F4;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(OfferModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update offer set ");
            strSql.Append("COMPANYID=@COMPANYID,");
            strSql.Append("USERID=@USERID,");
            strSql.Append("USERNAME=@USERNAME,");
            strSql.Append("REALNAME=@REALNAME,");
            strSql.Append("TITLE=@TITLE,");
            strSql.Append("UNIT=@UNIT,");
            strSql.Append("SPECIFICATIONSSTR=@SPECIFICATIONSSTR,");
            strSql.Append("VIPPRICE=@VIPPRICE,");
            strSql.Append("MARKETPRICE=@MARKETPRICE,");
            strSql.Append("MINPRICE=@MINPRICE,");
            strSql.Append("PIFAPRICE=@PIFAPRICE,");
            strSql.Append("PCID=@PCID,");
            strSql.Append("INTRO=@INTRO,");
            strSql.Append("AREA=@AREA,");
            strSql.Append("RECOMMEND=@RECOMMEND,");
            strSql.Append("SORT=@SORT,");
            strSql.Append("IMAGES=@IMAGES,");
            strSql.Append("INVENTORYCOUNT=@INVENTORYCOUNT,");
            strSql.Append("TRADETOTAL=@TRADETOTAL,");
            strSql.Append("STATE=@STATE,");
            strSql.Append("MEMO=@MEMO,");
            strSql.Append("INSERTTIME=@INSERTTIME,");
            strSql.Append("MODIFYTIME=@MODIFYTIME,");
            strSql.Append("F1=@F1,");
            strSql.Append("F2=@F2,");
            strSql.Append("F3=@F3,");
            strSql.Append("F4=@F4");
            strSql.Append(" where OFFERID=@OFFERID");
            MySqlParameter[] parameters = {
					new MySqlParameter("@COMPANYID", MySqlDbType.Int32,11),
					new MySqlParameter("@USERID", MySqlDbType.Int32,11),
					new MySqlParameter("@USERNAME", MySqlDbType.VarChar,36),
					new MySqlParameter("@REALNAME", MySqlDbType.VarChar,36),
					new MySqlParameter("@TITLE", MySqlDbType.VarChar,512),
					new MySqlParameter("@UNIT", MySqlDbType.VarChar,32),
					new MySqlParameter("@SPECIFICATIONSSTR", MySqlDbType.VarChar,64),
					new MySqlParameter("@VIPPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@MARKETPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@MINPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@PIFAPRICE", MySqlDbType.Decimal,10),
					new MySqlParameter("@PCID", MySqlDbType.Int32,11),
					new MySqlParameter("@INTRO", MySqlDbType.VarChar,1024),
					new MySqlParameter("@AREA", MySqlDbType.VarChar,64),
					new MySqlParameter("@RECOMMEND", MySqlDbType.Int32,11),
					new MySqlParameter("@SORT", MySqlDbType.Int32,11),
					new MySqlParameter("@IMAGES", MySqlDbType.VarChar,256),
					new MySqlParameter("@INVENTORYCOUNT", MySqlDbType.Int32,11),
					new MySqlParameter("@TRADETOTAL", MySqlDbType.Int32,11),
					new MySqlParameter("@STATE", MySqlDbType.Int32,11),
					new MySqlParameter("@MEMO", MySqlDbType.VarChar,256),
					new MySqlParameter("@INSERTTIME", MySqlDbType.DateTime),
					new MySqlParameter("@MODIFYTIME", MySqlDbType.DateTime),
					new MySqlParameter("@F1", MySqlDbType.VarChar,256),
					new MySqlParameter("@F2", MySqlDbType.VarChar,256),
					new MySqlParameter("@F3", MySqlDbType.VarChar,256),
					new MySqlParameter("@F4", MySqlDbType.VarChar,256),
					new MySqlParameter("@OFFERID", MySqlDbType.Int32,11)};
            parameters[0].Value = model.COMPANYID;
            parameters[1].Value = model.USERID;
            parameters[2].Value = model.USERNAME;
            parameters[3].Value = model.REALNAME;
            parameters[4].Value = model.TITLE;
            parameters[5].Value = model.UNIT;
            parameters[6].Value = model.SPECIFICATIONSSTR;
            parameters[7].Value = model.VIPPRICE;
            parameters[8].Value = model.MARKETPRICE;
            parameters[9].Value = model.MINPRICE;
            parameters[10].Value = model.PIFAPRICE;
            parameters[11].Value = model.PCID;
            parameters[12].Value = model.INTRO;
            parameters[13].Value = model.AREA;
            parameters[14].Value = model.RECOMMEND;
            parameters[15].Value = model.SORT;
            parameters[16].Value = model.IMAGES;
            parameters[17].Value = model.INVENTORYCOUNT;
            parameters[18].Value = model.TRADETOTAL;
            parameters[19].Value = model.STATE;
            parameters[20].Value = model.MEMO;
            parameters[21].Value = model.INSERTTIME;
            parameters[22].Value = model.MODIFYTIME;
            parameters[23].Value = model.F1;
            parameters[24].Value = model.F2;
            parameters[25].Value = model.F3;
            parameters[26].Value = model.F4;
            parameters[27].Value = model.OFFERID;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int OFFERID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from offer ");
            strSql.Append(" where OFFERID=@OFFERID");
            MySqlParameter[] parameters = {
					new MySqlParameter("@OFFERID", MySqlDbType.Int32)
			};
            parameters[0].Value = OFFERID;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string OFFERIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from offer ");
            strSql.Append(" where OFFERID in (" + OFFERIDlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
